package mwa.cs.dao;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;

public class SMPackageDAO {
	
	private Database db;

	public SMPackageDAO(Database db) {
		this.db = db;
	}
	//Find All
    public List<Map<String, Object>> findAll(int pnStart,int pnAmount) {
    	
    	int vnStart = pnStart+1;
    	int vnEnd = vnStart+pnAmount-1;
    	String vaSql = "";
    	
    	if(pnAmount==-1){
    		vaSql = " SELECT * FROM SM_PACKAGE ORDER BY PACKAGE_ID ASC";
            List<Map<String, Object>> result = db.queryList(vaSql);
            if (result != null) {
                return result;
            } else {
                return null;
            }
    	}
    	else{
	    	vaSql = "" +
    		"SELECT * " +
    		"FROM " +
    		"( " +
    		"SELECT ROWNUM r,sm.* " +
    		"FROM " +
    		"( " +
    		"SELECT * " +
    		"FROM SM_PACKAGE  " +
    		"ORDER BY PACKAGE_ID ASC " +
    		") sm " +
    		"WHERE ROWNUM <="+vnEnd+" "+
    		") " +
    		"WHERE r>="+vnStart+" ";

	List<Map<String, Object>> result = db.queryList(vaSql);
	  		if (result != null) {
          		return result;
      		} else {
          		return null;
      		}
    	}
    }
    
    //Count all
 public int countAll(){
    	
		String vaSql = " "+
        		"SELECT COUNT(*) " +
        		"FROM SM_PACKAGE  ";
    	Map<String, Object> result = db.querySingle(vaSql);
        if (result != null) {
        	return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
    }
    
    public boolean isDuplicate(String paPcode,String paPname){
    	String vaSql = "select count(*) from SM_package where package_id = "+paPcode+" or PACKAGE_NAME = '"+paPname+"' ";

    	Map<String, Object> result = db.querySingle(vaSql);
        int count =  Integer.parseInt(result.get("COUNT(*)").toString());
        if(count>0){
        	return true;
        }
        else{
        	return false;
        }
    }
    
    //Add new row
    public boolean addSMPackage(String paUserId,String paPcode,String paName) {
    	
    	DateFormat voDateFormat = new SimpleDateFormat("yyyyMMdd");
		DateFormat voTimeFormat = new SimpleDateFormat("HHmmss");
		Date voD = new Date();    	
    	
   	 String vaSql = "INSERT INTO SM_PACKAGE (PACKAGE_ID,PACKAGE_NAME,STATUS,INSERT_USER_ID,INSERT_DATE,INSERT_TIME,"
   	 								+ "UPDATE_USER_ID,UPDATE_DATE,UPDATE_TIME) "
   			 						+"VALUES("+paPcode+",'"+paName+"','N',"+paUserId+","+voDateFormat.format(voD)+","+voTimeFormat.format(voD)+",0,NULL,NULL) ";
   			try {
   
   				db.insert(vaSql);
   				return true;
   			}catch (Exception e) {
   				return false;
   	        }
   			
   	}
    
    //Update the row
    public boolean updateSMPackage(String paUserId,String paPcode,String paName) {
   	 
    	DateFormat vodDateFormat = new SimpleDateFormat("yyyyMMdd");
		DateFormat voTimeFormat = new SimpleDateFormat("HHmmss");
		Date voD = new Date();  
    	
   	String vaSql  = " UPDATE SM_PACKAGE SET " +
   				  " PACKAGE_NAME  = '"+paName+"' , " +
   				  " UPDATE_USER_ID = "+paUserId+" , "+
   				  " UPDATE_DATE = "+vodDateFormat.format(voD)+" , " +
   				  " UPDATE_TIME = "+voTimeFormat.format(voD)+" " +
   				  " WHERE " + 
   				  " PACKAGE_ID  = "+paPcode+" ";
   	
   	 try {
				db.update(vaSql);
				return true;
							
		}catch (Exception e) {
			return false;
		}
   }
    
    //Delete a row
    public boolean deleteSMPackage(String paPcode) {
        String vaSql = "DELETE FROM SM_PACKAGE WHERE PACKAGE_ID = "+paPcode+" ";
        try {
            db.update(vaSql);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
    
    
    

}
